package com.iris.live.services.report.sources;

import com.google.common.collect.Maps;
import com.iris.live.services.common.StringUtils;
import com.iris.live.services.data.models.ReportConfig;
import com.iris.live.services.report.data.BaseReportDataSource;
import com.iris.live.services.report.data.ReportContext;
import org.springframework.stereotype.Component;

import java.util.*;

/**
 * Created by lichao on 2017/8/24.
 */
@Component(value = "ClueManagementDataSource")
public class ClueManagementDataSource extends BaseReportDataSource {
    @Override
    public List getData(ReportContext context, List<ReportConfig> config) throws Exception {
        Map info=context.getParams();
        replaceDateParam(info);
        String lineType=info.get("displayType").toString();
        List<Map> datas =new ArrayList<>();
        if("1".equals(lineType)){
            datas=  getTimeLineData(info);
        }
        else if("2".equals(lineType)){
            datas=  getOrgLineData(info);
        }else if("3".equals(lineType)){
            datas = getModelLineData(info);
        }else{
            datas = getChannelLineData(info);
        }

        super.brandGroup(context ,datas);
        return  datas;
    }

    private List getTimeLineData(Map info){
        String sql = "select     permit,   " +
                "                `date`,   " +
                "                sum(if(a.cust_channel<>'散客', a.new_clue_num,clue_inroom_num)) new_clue_num,   " +
                "                sum(a.clue_inroom_num) clue_inroom_num,   " +
                "                sum(a.clue_quotation_num) clue_quotation_num,   " +
                "                sum(a.clue_order_num) clue_order_num,   " +
                "                sum(a.clue_retail_num) clue_retail_num,   " +
                "                sum(a.clue_test_drive_num) clue_test_drive_num,   " +
                "                品牌,   " +
                "                cust_channel   " +
                "from lsh经销商表  allset   " +
                "join(   " +
                "                select a.permit," +
                "                if(b.品牌='' or b.品牌 is null,'其他',b.品牌) 品牌,   " +
                "                a.cust_channel,   " +
                "                RIGHT(a.sale_month,2) `date`,   " +
                "                sum(a.new_clue_num) new_clue_num,   " +
                "                sum(a.first_inroom_clue_num) clue_inroom_num,   " +
                "                sum(a.quotation_num) clue_quotation_num,   " +
                "                sum(a.order_num) clue_order_num,   " +
                "                sum(a.retail_num) clue_retail_num,   " +
                "                sum(a.first_inroom_drive_num) clue_test_drive_num   " +
                "         from    " +
                "         t_rpt_data_sale_info a   " +
                "         LEFT JOIN lsh车型 b   " +
                "         ON a.vehicle_model_id = b.车型编号 " +
                "         where LEFT(a.sale_month,4) = :year   " +
                "         and 1=1 " +
                "         GROUP BY    " +
                "         RIGHT(a.sale_month,2),a.permit   " +
                "      ) a    " +
                " ON a.permit=allset.许可_BMBS " +
                " where 2=2    " +
                " group by date ";
        Map map = Maps.newHashMap();
        StringBuffer conditionSql = new StringBuffer("1=1");

        String orgTerm = getOrgTerms("allset", info, map);
        if(StringUtils.isNotNullOrBlank(orgTerm)){
            sql= sql.replace("2=2",orgTerm);
        }
        /*String permit = info.get("permit") == null ? "" : info.get("permit").toString();
        if(! StringUtils.isNullOrBlank(permit) && !"LSH".equals(permit)){
            sql = sql.replace("2=2", " allset.大区= :permit  or  allset.大区_EN=:permit or " +
                    "   allset.CTA=:permit or allset.CTA_EN=:permit  or allset.许可_BMBS=:permit");
            map.put("permit", permit);
        }*/

        String selBrand = info.get("selBrand") == null ? "" : info.get("selBrand").toString();
        if(StringUtils.isNotNullOrBlank(selBrand) && !"all".equals(selBrand) && !"全部".equals(selBrand)){
            conditionSql.append(" and b.品牌 = :model");
            map.put("model",selBrand);
        }

        String selChannel = info.get("selChannel") == null ? "" : info.get("selChannel").toString();
        if(StringUtils.isNotNullOrBlank(selChannel) && !"all".equals(selChannel) && !"全部".equals(selChannel)){
                map.put("selChannel",selChannel);
                conditionSql.append(" and a.cust_channel = :selChannel");
        }

        sql = sql.replace("1=1",conditionSql);
        map.put("year",info.get("selectYear"));
        return this.getList(sql,map);
    }

    private List getOrgLineData(Map info){
        String sql = "select     'DCH' 集团,allset.许可_LSH,allset.大区 AS 大区, allset.大区_EN AS 大区_EN,   " +
                "                IF ( isnull(allset.CTA) OR allset.CTA = '', '其他cta', allset.CTA ) AS CTA,    " +
                "                IF (isnull(allset.CTA_EN) OR allset.CTA_EN = '', 'Other CTA', allset.CTA_EN ) AS CTA_EN,   " +
                "                allset.许可_BMBS AS 许可_BMBS,   " +
                "                allset.经销商名 AS 经销商名,   " +
                "                allset.经销商名_EN AS 经销商名_EN,   " +
                "                allset.SalesCode AS salesCode,   " +
                "                sum(if(a.cust_channel<>'散客', a.new_clue_num,clue_inroom_num)) new_clue_num,   " +
                "                sum(a.clue_inroom_num) clue_inroom_num,   " +
                "                sum(a.clue_quotation_num) clue_quotation_num,   " +
                "                sum(a.clue_order_num) clue_order_num,   " +
                "                sum(a.clue_retail_num) clue_retail_num,   " +
                "                sum(a.clue_test_drive_num) clue_test_drive_num,   " +
                "                品牌,   " +
                "                cust_channel   " +
                "from lsh经销商表  allset   " +
                "join(   " +
                "         select a.permit," +
                "                if(b.品牌='' or b.品牌 is null,'其他',b.品牌) 品牌,   " +
                "                a.cust_channel,   " +
                "                RIGHT(a.sale_month,2) `date`,   " +
                "                sum(a.new_clue_num) new_clue_num,   " +
                "                sum(a.first_inroom_clue_num) clue_inroom_num,   " +
                "                sum(a.quotation_num) clue_quotation_num,   " +
                "                sum(a.order_num) clue_order_num,   " +
                "                sum(a.retail_num) clue_retail_num,   " +
                "                sum(a.first_inroom_drive_num) clue_test_drive_num   " +
                "         from    " +
                "         t_rpt_data_sale_info a   " +
                "         LEFT JOIN lsh车型 b   " +
                "         ON a.vehicle_model_id = b.车型编号 " +
                "         where 1=1 " +
                "         GROUP BY a.permit   " +
                "  ) a ON a.permit=allset.许可_BMBS " +
                "   GROUP BY permit";
        Map map = Maps.newHashMap();
        StringBuffer conditionSql = new StringBuffer("1=1");

        String startMonth = info.get("startMonth") == null ? "" : info.get("startMonth").toString();
        String endMonth =  info.get("endMonth") == null ? "" : info.get("endMonth").toString();
        if (StringUtils.isNotNullOrBlank(startMonth) && StringUtils.isNotNullOrBlank(endMonth)) {
            conditionSql.append(" and sale_month between :startMonth and :endMonth ");
            map.put("startMonth", startMonth);
            map.put("endMonth", endMonth);
        }

        String selBrand = info.get("selBrand") == null ? "" : info.get("selBrand").toString();
        if(StringUtils.isNotNullOrBlank(selBrand) && !"all".equals(selBrand) && !"全部".equals(selBrand)){
            conditionSql.append(" and b.品牌 = :model");
            map.put("model",selBrand);
        }

        String selChannel = info.get("selChannel") == null ? "" : info.get("selChannel").toString();
        if(StringUtils.isNotNullOrBlank(selChannel) && !"all".equals(selChannel) && !"全部".equals(selChannel)){
            map.put("selChannel",selChannel);
            conditionSql.append(" and a.cust_channel = :selChannel");
        }

        sql = sql.replace("1=1",conditionSql);
        return this.getList(sql,map);
    }

    private List getModelLineData(Map info) {
        String sql = "select     permit,   " +
                "                sum(if(a.cust_channel<>'散客', a.new_clue_num,clue_inroom_num)) new_clue_num,   " +
                "                sum(a.clue_inroom_num) clue_inroom_num,   " +
                "                sum(a.clue_quotation_num) clue_quotation_num,   " +
                "                sum(a.clue_order_num) clue_order_num,   " +
                "                sum(a.clue_retail_num) clue_retail_num,   " +
                "                sum(a.clue_test_drive_num) clue_test_drive_num,   " +
                "                品牌,   " +
                "                cust_channel   " +
                " from lsh经销商表  allset   " +
                " join(   " +
                "          select a.permit," +
                "                if(b.品牌='' or b.品牌 is null,'其他',b.品牌) 品牌,   " +
                "                a.cust_channel cust_channel,   " +
                "                RIGHT(a.sale_month,2) `date`,   " +
                "                sum(a.new_clue_num) new_clue_num,   " +
                "                sum(a.first_inroom_clue_num) clue_inroom_num,   " +
                "                sum(a.quotation_num) clue_quotation_num,   " +
                "                sum(a.order_num) clue_order_num,   " +
                "                sum(a.retail_num) clue_retail_num,   " +
                "                sum(a.first_inroom_drive_num) clue_test_drive_num   " +
                "         from    " +
                "         t_rpt_data_sale_info a   " +
                "         LEFT JOIN lsh车型 b   " +
                "         ON a.vehicle_model_id = b.车型编号 " +
                "         where    " +
                "         1=1 " +
                "         GROUP BY    " +
                "         b.品牌 ,permit   " +
                "    ) a    " +
                " ON a.permit=allset.许可_BMBS  " +
                " where  2=2   " +
                " group by 品牌 ";
        Map map = Maps.newHashMap();
        StringBuffer conditionSql = new StringBuffer("1=1");

        String orgTerm = getOrgTerms("allset", info, map);
        if(StringUtils.isNotNullOrBlank(orgTerm)){
            sql= sql.replace("2=2",orgTerm);
        }

        /*String permit = info.get("permit") == null ? "" : info.get("permit").toString();
        if(! StringUtils.isNullOrBlank(permit) && !"LSH".equals(permit)){
            sql = sql.replace("2=2", " allset.大区= :permit  or  allset.大区_EN=:permit or " +
                    "    allset.CTA=:permit or allset.CTA_EN=:permit  or allset.许可_BMBS=:permit");
            map.put("permit", permit);
        }*/

        String selChannel = info.get("selChannel") == null ? "" : info.get("selChannel").toString();
        if(StringUtils.isNotNullOrBlank(selChannel) && !"all".equals(selChannel) && !"全部".equals(selChannel)){
            map.put("selChannel",selChannel);
            conditionSql.append(" and a.cust_channel = :selChannel");
        }

        String startMonth = info.get("startMonth") == null ? "" : info.get("startMonth").toString();
        String endMonth =  info.get("endMonth") == null ? "" : info.get("endMonth").toString();
        if (StringUtils.isNotNullOrBlank(startMonth) && StringUtils.isNotNullOrBlank(endMonth)) {
            conditionSql.append(" and sale_month between :startMonth and :endMonth ");
            map.put("startMonth", startMonth);
            map.put("endMonth", endMonth);
        }

        sql = sql.replace("1=1", conditionSql);
        return this.getList(sql, map);
    }
    private List getChannelLineData(Map info) {
        String sql = "select   " +
                "                permit,   " +
                "                a.cust_channel,   " +
                "                sum(if(cust_channel<>'散客', a.new_clue_num,clue_inroom_num)) new_clue_num,   " +
                "                sum(a.clue_inroom_num) clue_inroom_num,   " +
                "                sum(a.clue_quotation_num) clue_quotation_num,   " +
                "                sum(a.clue_order_num) clue_order_num,   " +
                "                sum(a.clue_retail_num) clue_retail_num,   " +
                "                sum(a.clue_test_drive_num) clue_test_drive_num   " +
                "                from lsh经销商表  allset   " +
                "  join(   " +
                "          select    " +
                "                a.permit,   " +
                "                case  when a.cust_channel='' or a.cust_channel is null then '其他' when a.cust_channel = '网络' then concat(a.cust_channel,'_',a.cust_source) else a.cust_channel end cust_channel,   " +
                "                sum(a.new_clue_num) new_clue_num,   " +
                "                sum(a.first_inroom_clue_num) clue_inroom_num,   " +
                "                sum(a.quotation_num) clue_quotation_num,   " +
                "                sum(a.order_num) clue_order_num,   " +
                "                sum(a.retail_num) clue_retail_num,   " +
                "                sum(a.first_inroom_drive_num) clue_test_drive_num   " +
                "         from    " +
                "         t_rpt_data_sale_info a   " +
                "         LEFT JOIN lsh车型 b   " +
                "         ON a.vehicle_model_id = b.车型编号    " +
                "         where   " +
                "                1=1    " +
                "         GROUP BY    " +
                "               a.permit, a.cust_channel,a.cust_source   " +
                "     ) a    " +
                " ON a.permit=allset.许可_BMBS     " +
                " where 2=2   " +
                " group by cust_channel";
        Map map = Maps.newHashMap();
        StringBuffer conditionSql = new StringBuffer("1=1");

        String orgTerm = getOrgTerms("allset", info, map);
        if(StringUtils.isNotNullOrBlank(orgTerm)){
            sql= sql.replace("2=2",orgTerm);
        }

        /*String permit = info.get("permit") == null ? "" : info.get("permit").toString();
        if(! StringUtils.isNullOrBlank(permit) && !"LSH".equals(permit)){
            sql = sql.replace("2=2", " allset.大区= :permit  or  allset.大区_EN=:permit or " +
                    "     allset.CTA=:permit or allset.CTA_EN=:permit  or allset.许可_BMBS=:permit");
            map.put("permit", permit);
        }*/

        String selBrand = info.get("selBrand") == null ? "" : info.get("selBrand").toString();
        if(StringUtils.isNotNullOrBlank(selBrand) && !"all".equals(selBrand) && !"全部".equals(selBrand)){
            conditionSql.append(" and b.品牌 = :model");
            map.put("model",selBrand);
        }

        String startMonth = info.get("startMonth") == null ? "" : info.get("startMonth").toString();
        String endMonth =  info.get("endMonth") == null ? "" : info.get("endMonth").toString();
        if (StringUtils.isNotNullOrBlank(startMonth) && StringUtils.isNotNullOrBlank(endMonth)) {
            conditionSql.append(" and sale_month between :startMonth and :endMonth ");
            map.put("startMonth", startMonth);
            map.put("endMonth", endMonth);
        }

        sql = sql.replace("1=1", conditionSql);
        return this.getList(sql, map);
    }

    private void replaceDateParam(Map info){
        String startDay = info.get("startDay") == null ? "" : info.get("startDay").toString();
        String endDay =  info.get("endDay") == null ? "" : info.get("endDay").toString();
        if (StringUtils.isNotNullOrBlank(startDay) && StringUtils.isNotNullOrBlank(endDay)) {
            String startMonth = startDay.substring(0, 7).replace("-","");
            String endMonth = endDay.substring(0,7).replace("-","");
            info.put("startMonth",startMonth);
            info.put("endMonth",endMonth);
        }
    }

}
